package com.hnkywl.sts.dao.organ;

import com.hnkywl.sts.entity.htgl.Fkfs;
import com.hnkywl.sts.entity.organ.Gc;
import com.hnkywl.sts.entity.organ.Qyjg;
import com.hnkywl.sts.util.Tools;
import net.ruixin.orm.hibernate.Page;
import net.ruixin.orm.hibernate.SimpleHibernateTemplate;
import org.hibernate.Criteria;
import org.hibernate.SQLQuery;
import org.hibernate.criterion.CriteriaSpecification;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Property;
import org.hibernate.criterion.Restrictions;
import org.hibernate.transform.AliasToEntityMapResultTransformer;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * Created with IntelliJ IDEA.
 * User: jerry
 * Date: 13-3-28
 * Time: 上午10:07
 * To change this template use File | Settings | File Templates.
 */
@Repository
public class FkfsDao extends SimpleHibernateTemplate<Fkfs, Long> {
    public List<Map> findFkfsByQyjgId(Long qyjgId) {
        String query = " select fs.id   as \"id\",\n" +
                "       fs.fksj          as \"fksj\",\n" +
                "       fs.fkll          as \"fkll\",\n" +
                "       fs.ht_id         as \"ht.id\",\n" +
                "       fs.fksjstart     as \"fksjStart\",\n" +
                "       fs.qyjg_id       as \"qyjg.id\",       \n" +
                "       fs.fklx_sjzdz_id as \"fklx.id\",\n" +
                "       fs.fkgs as \"fkgs\"\n" +
                "       from t_biz_fkfs fs\n" +
                "       left join t_sys_gsjg q \n" +
                "          on fs.qyjg_id = q.id\n" +
                "        where fs.qyjg_id = :qyjgId order by fs.fklx_sjzdz_id,fs.fksjstart ";
        SQLQuery sqlQuery = getSession().createSQLQuery(query);
        sqlQuery.setLong("qyjgId", qyjgId);
        sqlQuery.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        return sqlQuery.list();
    }

    public List<Fkfs> findAllFkfsListByQyjgId(Long qyjgId) {
        return find("from Fkfs where qyjg.id = ?", qyjgId);
    }

    public List<Map> findFkfsByhtId(Long htId) {
        String query = " select fs.id   as \"id\",\n" +
                "       fs.fksj          as \"fksj\",\n" +
                "       fs.fkll          as \"fkll\",\n" +
                "       fs.ht_id         as \"ht.id\",\n" +
                "       fs.fksjstart     as \"fksjStart\",\n" +
                "       fs.qyjg_id       as \"qyjg.id\",       \n" +
                "       fs.fklx_sjzdz_id as \"fklx.id\",\n" +
                "       z.z as \"fklx.z\",\n" +
                "       fs.fkgs as \"fkgs\"\n" +
                "       from t_biz_fkfs fs\n" +
                "       left join t_biz_ht q \n" +
                "          on fs.ht_id = q.id\n" +
                "       left join t_sys_sjzdz z \n" +
                "          on fs.fklx_sjzdz_id = z.id\n" +
                "        where fs.ht_id = :htId order by fs.fklx_sjzdz_id,fs.fksjstart ";
        SQLQuery sqlQuery = getSession().createSQLQuery(query);
        sqlQuery.setLong("htId", htId);
        sqlQuery.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        return sqlQuery.list();
    }


    /**
     * 计算代理费(交易系统接口)
     *
     * @param days
     * @param fklx
     * @param weight
     * @param customName
     * @return
     */
    public Double findFkgsValue(String customName, Integer days, Integer fklx, Double weight) {
        Connection con = null;
        CallableStatement call = null;
        Double result;
        try {
            con = getSession().connection();
            System.out.print("R_BIZ_DLF_KH...START...");
            call = con.prepareCall("{Call R_BIZ_DLF_KH(?,?,?,?,?)}");
            call.setString(1, customName);
            call.setInt(2, fklx);
            call.setDouble(3, weight);
            call.setInt(4, days);
            call.registerOutParameter(5, Types.DOUBLE);
            call.execute();
            result = call.getDouble(5);
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException("数据异常或者没有找到数据！");
        } finally {
            closeResource(null, call, con);
        }
        return new BigDecimal(result).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue();

    }

    private void closeResource(ResultSet rs, Statement s, Connection con) {
        if (rs != null)
            try {
                rs.close();
            } catch (Exception e) {
                e.printStackTrace();
                throw new RuntimeException(e.getMessage());
            }
        if (s != null)
            try {
                s.close();
            } catch (Exception e) {
                e.printStackTrace();
                throw new RuntimeException(e.getMessage());
            }
        if (con != null)
            try {
                con.close();
            } catch (Exception e) {
                e.printStackTrace();
                throw new RuntimeException(e.getMessage());
            }
    }

    public List<Map> findAgentFeeRuleByGsjgId(Long gsjg_id) {
        String sql = "select (select sz.z from t_sys_sjzdz sz where sz.id = f.fklx_sjzdz_id) as \"fklx\"," +
                "  f.fksjstart || '---' ||f.fksj || ' 天' as \"range\" , '代理费标准：'|| 10*f.fkgs ||'元/吨' as \"rate\"" +
                "  from t_biz_fkfs f where f.qyjg_id = ? " +
                "  union all " +
                "  select distinct (select sz.z from t_sys_sjzdz sz where sz.id = fk.fklx_sjzdz_id) as \"fklx\"," +
                "  '超出10天' as \"range\" ,'代理费标准：1元/吨*天' as \"rate\"  from t_biz_fkfs fk where fk.qyjg_id =?";
        return (List<Map>) this.createSQLQuery(sql, gsjg_id, gsjg_id).setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE).list();
    }
}
